Create Staging Table in Staging Database and Populate the Staging Tables 10
Download the SQL File attached to the
Run Script that is attached to create your Staging tables in the Staging Database. Once ran, the tables will be made up of these columns.
*Using SSIS Toolbox, Create "LoadStagingTable" Package, the following components will be drag in the Control Flow Dashboard;
C# Code for Script Task " GetJSONFileName "
#region Namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.IO; #endregion
public void Main()
{
// TODO: Add your code here
string filepath;
string filename;
filepath = Dts.Variables["User::JSONFilePath"].Value.ToString();
filename = Path.GetFileName(filepath);
Dts.Variables["User::JSONFileName"].Value = filename;
// MessageBox.Show(filename);
Dts.TaskResult = (int)ScriptResults.Success;
}
C# Code for Class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace IncidentsNameSpace
{
class Incidents
{
public string IncidentNumber { get; set; }
public string IncidentType { get; set; }
public string IncidentDate { get; set; }
public string IncidentLocation { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Zip { get; set; }
public string ReportingDistrict { get; set; }
public string GeoLocation { get; set; }
public string HandlingUnitID { get; set; }
public string HandlingUnitName { get; set; }
public string NumberOfInvolvedDeputies { get; set; }
public string DeputyRace { get; set; }
public string NumberOfSuspects { get; set; }
public string SuspectRace { get; set; }
public string NumberOfSuspectWounded { get; set; }
public string NumberOfSuspectDeceased { get; set; }
public string WeaponInvolvedCategory { get; set; }
public string WeaponInvolvedCategoryDesc { get; set; }
public string Latitude { get; set; }
public string Longitude { get; set; }
}
}
#region Namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Collections.Generic; using System.Text; using System.Web.Script.Serialization; using System.IO; using IncidentsNameSpace; #endregion
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
String jsonFileContent = File.ReadAllText(@"C: \Users\m_kol\Documents\DWH\12.Project1\MyProject\Json File\20201114.JSON");
JavaScriptSerializer js = new JavaScriptSerializer() { MaxJsonLength = 86753090 };
List<Incidents> IncidentDetails = js.Deserialize<List<Incidents>>(jsonFileContent);
foreach (Incidents IncidentList in IncidentDetails)
{
Output0Buffer.AddRow();
Output0Buffer.IncidentNumber = IncidentList.IncidentNumber;
Output0Buffer.IncidentType = IncidentList.IncidentType;
Output0Buffer.IncidentDate = IncidentList.IncidentDate;
Output0Buffer.IncidentLocation = IncidentList.IncidentLocation;
Output0Buffer.City = IncidentList.City;
Output0Buffer.State = IncidentList.State;
Output0Buffer.Zip = IncidentList.Zip;
Output0Buffer.ReportingDistrict = IncidentList.ReportingDistrict;
Output0Buffer.GeoLocation = IncidentList.GeoLocation;
Output0Buffer.HandlingUnitID = IncidentList.HandlingUnitID;
Output0Buffer.HandlingUnitName = IncidentList.HandlingUnitName;
Output0Buffer.NumberOfInvolvedDeputies = IncidentList.NumberOfInvolvedDeputies;
Output0Buffer.DeputyRace = IncidentList.DeputyRace;
Output0Buffer.NumberOfSuspects = IncidentList.NumberOfSuspects;
Output0Buffer.SuspectRace = IncidentList.SuspectRace;
Output0Buffer.NumberOfSuspectWounded = IncidentList.NumberOfSuspectWounded;
Output0Buffer.NumberOfSuspectDeceased = IncidentList.NumberOfSuspectDeceased;
Output0Buffer.WeaponInvolvedCategory = IncidentList.WeaponInvolvedCategory;
Output0Buffer.WeaponInvolvedCategoryDesc = IncidentList.WeaponInvolvedCategoryDesc;
Output0Buffer.Latitude = IncidentList.Latitude;
Output0Buffer.Longitude = IncidentList.Longitude;
}
}
}Expression Codes For Derived Column Transformation
FileDate SUBSTRING(@[User::JSONFileName],5,2) + "/" + SUBSTRING(@[User::JSONFileName],7,2) + "/" + SUBSTRING(@[User::JSONFileName],1,4)
DeputyRace REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DeputyRace,"ASIAN-PACIFIC,ISL","A"),"FILIPINO","F"),"BLACK","B"),"WHITE","W"),"HISPANIC","H"),"ASIAN-PACIFIC-ISL","A")
SuspectRace REPLACENULL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SuspectRace,"ASIAN-PACIFIC,ISL","A"),",,HISPANIC","H"),"BLACK","B"),"WHITE","W"),"HISPANIC","H"),"ASIAN-PACIFIC-ISL","A") == "" ? NULL(DT_WSTR,255) : ((DT_WSTR,255)REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SuspectRace,"ASIAN-PACIFIC,ISL","A"),",,HISPANIC","H"),"BLACK","B"),"WHITE","W"),"HISPANIC","H"),"ASIAN-PACIFIC-ISL","A")),"UNKNOWN")